/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sims.io;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import sims.databaseconnection.Connector;
import sims.entity.Order;
import sims.entity.OrderDetail;
import sims.entity.User;

/**
 *
 * @author BinhMV
 */
public class StatisticsIO {

    private Connection conn;

    public ArrayList<Order> readOrder() throws Exception {
        ArrayList<Order> lstOrders = new ArrayList<Order>();
        conn = new Connector().connect();
        Statement request = conn.createStatement();
        ResultSet data = request.executeQuery("select * from [orders]");
        while (data.next()) {
            Order order = new Order();
            order.setOrderID(data.getInt("orderId"));
            order.setDatetime(data.getDate("datetimes"));
            order.setType(data.getString("type"));
            order.setStatus(data.getString("status"));
            order.setTotal(data.getFloat("total"));
            lstOrders.add(order);
        }
        request.close();
        conn.close();
        return lstOrders;
    }

    public ArrayList<Order> readOrderByTime(String time) throws Exception {
        ArrayList<Order> lstOrders = new ArrayList<Order>();
        conn = new Connector().connect();
        CallableStatement request = null;
       
           if( time.equals("Month")){
                request = conn.prepareCall("execute sp_billsByMonth");
           }
           else if( time.equals("Day"))  {
               request = conn.prepareCall("execute sp_billsByDate");
           } 
           else if( time.equals("Year"))  {
              request = conn.prepareCall("execute sp_billsByYear");
           } 
        ResultSet data = request.executeQuery();
        while (data.next()) {
            Order order = new Order();
            order.setOrderID(data.getInt("orderId"));
            order.setDatetime(data.getDate("datetimes"));
            order.setType(data.getString("type"));
            order.setStatus(data.getString("status"));
            order.setTotal(data.getFloat("total"));
            lstOrders.add(order);
        }
        request.close();
        conn.close();
        return lstOrders;
    }

    public int totalItemsReturn(String time) throws Exception {

        conn = new Connector().connect();
        CallableStatement request = null;
        if( time.equals("Month")){
                request = conn.prepareCall("execute sp_itemReturnByMonth");
           }
           else if( time.equals("Day"))  {
               request = conn.prepareCall("execute sp_itemReturnByDate");
           } 
           else if( time.equals("Year"))  {
              request = conn.prepareCall("execute sp_itemReturnByYear");
           } 
       
        ResultSet data = request.executeQuery();
        data.next();
        int result = data.getInt("result");
        request.close();
        conn.close();
        return result;
    }

    public ArrayList<OrderDetail> waitingBills() throws Exception {
        conn = new Connector().connect();
        Statement request = conn.createStatement();
        ResultSet data = request.executeQuery("select * from [view_waitingBills]");
        ArrayList<OrderDetail> lstDetails = new ArrayList<OrderDetail>();
        while (data.next()) {
            OrderDetail orderDetail = new OrderDetail();
            orderDetail.setOrderID(data.getInt("orderId"));
            orderDetail.setItemID(data.getInt("itemId"));
            orderDetail.setAmount(data.getInt("amount"));
            orderDetail.setPriceSell(data.getFloat("priceSell"));
            lstDetails.add(orderDetail);
        }
        request.close();
        conn.close();
        return lstDetails;
    }

    public void cancellOrders(ArrayList<OrderDetail> lstDetails) throws Exception {
        conn = new Connector().connect();
        PreparedStatement request = conn.prepareStatement("update item set quantityAvailable = quantityAvailable + ? where itemId = ?");
        for (int i = 0; i < lstDetails.size(); i++) {
            request.setInt(1, lstDetails.get(i).getAmount());
            request.setInt(2, lstDetails.get(i).getItemID());
            request.executeUpdate();
        }
        request = conn.prepareStatement("update orders set [status] = 'cancelled',type='sell' where orderId = ?");
        for (int i = 0; i < lstDetails.size(); i++) {
            request.setInt(1, lstDetails.get(i).getOrderID());
            request.executeUpdate();
        }
        request.close();
        conn.close();
    }
    
    public User getOrderInfo(int orderId) throws Exception{
        conn = new Connector().connect();
        CallableStatement request = conn.prepareCall("execute sp_orderInfo ?");
        request.setInt(1, orderId);
        ResultSet data = request.executeQuery();
        data.next();
        User user = new User(data.getInt("userId"), data.getString("username"), data.getString("pass"), data.getString("position"));

        return user;
    }
    
    public ArrayList<OrderDetail> getItemInOrder(int orderId) throws Exception{
        conn = new Connector().connect();
        CallableStatement request = conn.prepareCall("execute sp_itemInfo ?");
        request.setInt(1, orderId);
        ResultSet data = request.executeQuery();
        ArrayList<OrderDetail> lstDetails = new ArrayList<OrderDetail>();
        while (data.next()) {
            OrderDetail orderDetail = new OrderDetail();
            orderDetail.setOrderID(data.getInt("orderId"));
            orderDetail.setItemID(data.getInt("itemId"));
            orderDetail.setItemName(data.getString("itemName"));
            orderDetail.setAmount(data.getInt("amount"));
            orderDetail.setPriceSell(data.getFloat("priceSell"));
            lstDetails.add(orderDetail);
        }
        request.close();
        conn.close();
        return lstDetails;
    }
}